***
*Google Community Mobility**
*****
import delimited "HSSC replication data\Public data\Global_Mobility_Report.csv", clear
keep if sub_region_1==""
keep if sub_region_2==""

keep if metro_area==""

ren retail_and_recreation_percent_ch g_retail
ren grocery_and_pharmacy_percent_cha g_grocery
ren parks_percent_change_from_baseli g_park
ren transit_stations_percent_change_ g_transit
ren workplaces_percent_change_from_b  g_work
ren residential_percent_change_from_ g_home

gen time = date(date, "YMD")

ren country_region country

*switch to gallup names
replace country="Venezuela, RB" if country=="Venezuela"
replace country="Bosnia Herzegovina" if country=="Bosnia and Herzegovina"
replace country="Czech Republic" if country=="Czechia"
replace country="Egypt, Arab Rep." if country=="Egypt"
replace country="Hong Kong SAR, China" if country=="Hong Kong"
replace country="Ivory Coast" if country=="Côte d'Ivoire"
replace country="Korea, Rep." if country=="South Korea"
replace country="Kyrgyz Republic" if country=="Kyrgyzstan"
replace country="Macedonia, FYR" if country=="North Macedonia"
replace country="Russian Federation" if country=="Russia"
replace country="Slovak Republic" if country=="Slovakia"
replace country="Myanmar" if country=="Myanmar (Burma)"
replace country="Yemen, Rep." if country=="Yemen"

merge m:m country using "HSSC replication data\Public data\country_naming_conventions.dta", update replace
drop _merge

drop if countrycode==""
encode country, gen(id)

tsset time id

forval x=1/6 {
gen l`x'_retail=l`x'.g_retail
gen l`x'_work=l`x'.g_work
}
egen retail_7day=rowmean(g_retail l1_retail l2_retail l3_retail l4_retail l5_retail l6_retail)
egen work_7day=rowmean(g_work l1_work l2_work l3_work l4_work l5_work l6_work)

gen month=month(time)
gen year=year(time)

sort id time
gen obs=1

foreach x in retail_7day work_7day g_retail g_home g_work {
by id: gen RTM`x'=sum(`x')
by id: gen RTN`x'=sum(obs) 
gen RT`x'=RTM`x'/RTN`x'
drop RTM`x' RTN`x'
}

collapse (first) country (mean) RTretail_7day RTwork_7day RTg_retail RTg_home RTg_work  ///
retail_7day work_7day g_retail g_grocery g_park g_transit g_work g_home, by(month year countrycode)
save "HSSC replication data\Public data\Google_Mobility_Data_March31_2021_by_Month_Year_cum_to_date.dta", replace


